﻿using AuthTemplate.DTO.StudentCash;
using AuthTemplate.DTO.User;
using AuthTemplate.Entity;
using AuthTemplate.Infrastructure;
using AuthTemplate.Repository.Interface;
using AuthTemplate.Repository.Interface.IEntityRepository;
using AuthTemplate.Repository.Repositories;
using AuthTemplate.ViewModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AuthTemplate.Service
{
    public class StudentCashService : BaseService, IStudentCashService
    {
        private readonly IStudentCashRepository studentcashRepository;
        private readonly IUnitOfWork unitOfWork;
        public StudentCashService(IStudentCashRepository studentcashRepository,
            IUnitOfWork unitOfWork)
        {
            this.studentcashRepository = studentcashRepository;
            this.unitOfWork = unitOfWork;
        }

        public void UploadCash(List<string> paths , UploadCashInput input)
        {
            List<string> oldStudentNo = new List<string>();//已处理过的学号集合，每个学号仅仅处理一次，再次发现则跳过
            foreach (string path in paths)
            {
                NpioExcelHelper helper = new NpioExcelHelper(path);
                Dictionary<int, string> sheets = helper.ReturnSheetList();
                DataTable dt = helper.ExcelToDataTable(sheets[0], true);

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        StudentCash entity = new StudentCash();
                        entity.StudentNo = dr["学号"].ToString();
                        entity.Name = dr["姓名"].ToString();
                        entity.Amount = decimal.Parse(dr["研究生津贴"].ToString());
                        entity.Bz = dr["备注"].ToString();
                        entity.StudentGrade = input.grade;
                        entity.YearMonth = input.date;
                        entity.AmountCategory = input.type;
                        entity.AmountDetailType = "";
                        entity.CreateId = input.userid;
                        entity.CreateTime = DateTime.Now;
                        if (!oldStudentNo.Contains(entity.StudentNo))
                        {
                            List<string> oldentity = studentcashRepository.FindBy(s => s.StudentNo == entity.StudentNo && s.StudentGrade == entity.StudentGrade
                                && s.YearMonth == input.date && s.AmountCategory == entity.AmountCategory).Select(s => s.Id).ToList();
                            if (oldentity != null && oldentity.Count > 0)
                            {
                                entity.Id = oldentity[0];
                                studentcashRepository.Edit(entity);
                            }
                            else
                            {
                                studentcashRepository.Add(entity);
                            }


                            oldStudentNo.Add(entity.StudentNo);
                        }
                    }
                    unitOfWork.Commit();
                }
            }
            
        }

        public List<StudentCash> GetAllStudentCash(int page, int limit,DateTime startTime , DateTime endTime , string studentNo , out int count)
        {
            string startYearMonth = startTime.ToString("yyyy-MM");
            string endYearMonth = endTime.ToString("yyyy-MM");

            var studentCashs = studentcashRepository.FindBy(s => s.YearMonth.CompareTo(startYearMonth) >= 0 && s.YearMonth.CompareTo(endYearMonth) <= 0);
            if(!string.IsNullOrEmpty(studentNo))
            {
                studentCashs = studentCashs.Where(s => s.StudentNo == studentNo);
            }
            count = studentCashs.Count();
            studentCashs = studentCashs.OrderBy(s => s.YearMonth).ThenBy(s => s.StudentNo).Skip((page-1)*limit).Take(limit);
            return studentCashs.ToList();
        }

        public void EditStudentCash(StudentCash input)
        {
            studentcashRepository.Edit(input);
            unitOfWork.Commit();
        }

        public void DeleteStudentCash(string id)
        {
            StudentCash entity = new StudentCash();
            entity.Id = id;
            studentcashRepository.Delete(entity);
            unitOfWork.Commit();
        }

        public void DeleteStudentCashBatch(List<string> ids)
        {
            foreach(string id in ids)
            {
                StudentCash entity = new StudentCash();
                entity.Id = id;
                studentcashRepository.Delete(entity);
            }
            unitOfWork.Commit();
        }

        public List<CashExcelDto> GetCashExcelDto(string startTime, string endTime)
        {
            string sql = @"CREATE TEMPORARY TABLE temp (
	                            id INT PRIMARY KEY  AUTO_INCREMENT,
	                            StudentNo VARCHAR (100),
	                            Amount DECIMAL (11, 2)
                            ) AUTO_INCREMENT = 1 ;

                            INSERT INTO temp (StudentNo, Amount) SELECT
	                            sc.StudentNo,
	                            SUM(sc.Amount) Amount
                            FROM
	                            StudentCash sc
                            WHERE
	                            sc.YearMonth >= @startTime
                            AND sc.YearMonth <= @endTime
                            GROUP BY
	                            sc.StudentNo
                            ORDER BY
	                            sc.StudentNo;

                            SELECT
	                            t.id,
	                            t.StudentNo,
	                            si. NAME,
	                            si.IdCard,
	                            '否' isgdsr,
	                            '否' isqjxf,
	                            '是' isda,
	                            '无' iszxjh,
	                            t.Amount standAmount,
	                            t.Amount shouldAmount,
	                            t.Amount haveAmount,
	                            date_format(now(), '%Y%m%d') ffrq,
	                            0.00 zyAmount,
	                            t.Amount sjAmount
                            FROM
	                            temp t
                            LEFT JOIN StudentInfo si ON si.StudentNo = t.StudentNo;

                            DROP TABLE temp";
            Dictionary<string, object> paraDic = new Dictionary<string, object>();
            paraDic.Add("@startTime", startTime);
            paraDic.Add("@endTime", endTime);
            List<CashExcelDto> dtos = studentcashRepository.SqlQueryList<CashExcelDto>(sql, paraDic);
            return dtos.OrderBy(s=>s.id).ToList();
        }
    }

    public interface IStudentCashService
    {
        void UploadCash(List<string> paths , UploadCashInput input);

        List<StudentCash> GetAllStudentCash(int page, int limit, DateTime startTime, DateTime endTime, string studentNo, out int count);

        void EditStudentCash(StudentCash input);

        void DeleteStudentCash(string id);

        void DeleteStudentCashBatch(List<string> ids);

        List<CashExcelDto> GetCashExcelDto(string startTime, string endTime);
    }
}
